package db;

import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import sf.tools.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ResourceHistoryUtils {
    private static HikariDataSource ds = null;

    private static Logger logger = LoggerFactory.getLogger(ResourceHistoryUtils.class);


    static {
        ds = new HikariDataSource();
        ds.setJdbcUrl("jdbc:postgresql://10.14.69.150:5432/hosp_resource_history");
        ds.setUsername("postgres");
        ds.setPassword("hosp@pg2018");
        ds.setDriverClassName("org.postgresql.Driver");
    }


    private static ExecutorService cachedThreadPool = Executors.newCachedThreadPool();

    public static void main(String[] args) {
        Connection connection = null;
        try {
            String url = "jdbc:postgresql://10.14.69.150:5432/hosp_resource_history";
            String user = "postgres";
            String password = "123456";
            Class.forName("org.postgresql.Driver");

//            connection= DriverManager.getConnection(url, user, password);
            System.out.println("是否成功连接pg数据库" + connection);
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
                insert(1);
                return null;
            });
            cachedThreadPool.submit(() -> {
//                delete();
                return null;
            });
            cachedThreadPool.submit(() -> {
//                delete();
                return null;
            });
            cachedThreadPool.submit(() -> {
//                delete();
                return null;
            });
            cachedThreadPool.submit(() -> {
//                select();
                return null;
            });
            cachedThreadPool.submit(() -> {
//                select();
                return null;
            });
            cachedThreadPool.submit(() -> {
//                select();
                return null;
            });
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {

            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                    throw new RuntimeException(e);
                }
            }
        }
    }

    private static void insert(int index) throws Exception {
        String sql = "INSERT INTO cmdb_his_camera_"+index+"( res_id, property, batch_no, value, created) VALUES (?,?,?,?,?)";
        for (int i = 0; i < 2000; i++) {
            Connection conn = ds.getConnection();
            int count = 0;
            String resId = StringUtils.uuid32();

            conn.setAutoCommit(false); // 设置手动提交
            PreparedStatement psts = conn.prepareStatement(sql);
            long start = System.currentTimeMillis();
            boolean online = true;
            for (int j = 0; j < 1000; j++) {
                String batchNo = StringUtils.uuid32();
//                psts.setString(1, StringUtils.uuid32());
                psts.setString(1, resId);
                psts.setString(2, "onlineStatus");
                psts.setString(3, batchNo);
                psts.setString(4, online ? "1" : "0");
                psts.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                psts.addBatch();          // 加入批量处理

                psts.setString(1, resId);
                psts.setString(2, "onlineStatusChangeTime");
                psts.setString(3, batchNo);
                psts.setString(4, new Date().getTime() + "");
                psts.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
                psts.addBatch();          // 加入批量处理
                online = !online;
            }
            psts.executeBatch(); // 执行批量处理
            conn.commit();  // 提交
            logger.info("insert : " + (System.currentTimeMillis() - start) + "ms");
            conn.close();
        }
    }

    private static void select() throws Exception {
        String sql = "select * from ((select d1.res_id,d1.\"value\" \"onlineStatus\",d2.\"value\" \"onlineStatusChangeTime\" from\tcmdb_his_camera_1 d1,cmdb_his_camera_1 d2 where d1.res_id = '81e1b7c4146f441888ec6b758ae91adc' AND d1.property = 'onlineStatus' AND d1.batch_no = d2.batch_no AND d1.res_id = d2.res_id AND d2.property = 'onlineStatusChangeTime' and d1.created < '2019-08-26' order by d1.created desc limit 1) union all (\n" +
                "select d1.res_id,d1.\"value\" \"onlineStatus\",d2.\"value\" \"onlineStatusChangeTime\" from\tcmdb_his_camera_1 d1,cmdb_his_camera_1 d2 where d1.res_id = '81e1b7c4146f441888ec6b758ae91adc' AND d1.property = 'onlineStatus' AND d1.batch_no = d2.batch_no AND d1.res_id = d2.res_id AND d2.property = 'onlineStatusChangeTime' and d1.created < '2019-08-27' and  d1.created >= '2019-08-26' order by d1.created desc \n" +
                ")) t order by t.\"onlineStatusChangeTime\";";
        boolean run = true;
        while (run) {
            Connection conn = ds.getConnection();
            conn.setAutoCommit(true); // 设置手动提交
            long start = System.currentTimeMillis();
            PreparedStatement psts = conn.prepareStatement(sql);
            ResultSet rs = psts.executeQuery();
            rs.close();
            Thread.sleep(1L);
            conn.close();
            logger.info("select " + (System.currentTimeMillis() - start) + "ms");
        }
    }

    private static void delete() throws Exception {
        String sql = "delete  from cmdb_his_camera_1  where id = (select id from cmdb_his_camera_1 order by id limit 1)";
        boolean run = true;
        while (run) {
            Connection conn = ds.getConnection();
            conn.setAutoCommit(false); // 设置手动提交
            long start = System.currentTimeMillis();
            PreparedStatement psts = conn.prepareStatement(sql);
            psts.execute();
            Thread.sleep(1000L);
            conn.commit();  // 提交
            conn.close();
            logger.info("delete " + (System.currentTimeMillis() - start) + "ms");
        }
    }
}
